-- 1)Crie uma tabela de auditoria contendo os campos 
-- editora_id, data e observao
create table auditoria (editora_id int, data date, obs text);
-- drop table auditoria;

-- 2)Crie uma trigger para que toda vez que seja inserido um 
-- novo registro na tabela editora seja gravado um registro 
-- na tabela auditoria com a informao Registro inserido
-- drop trigger auditoria;

-- Criando a Trigger
CREATE TRIGGER auditoria AFTER INSERT ON editora
BEGIN
   INSERT INTO auditoria(editora_id,data, obs) 
   VALUES (new.id, datetime('now'), "Registro inserido" );
END;
-- drop trigger auditoria

-- Inserindo para testar!
insert into editora(id,nome) values (10,"Editora Brasil Brasileiro");

-- Conferindo na auditoria
select * from auditoria;


-- 3)Crie uma trigger para que toda vez que seja excludo um 
-- registro na tabela editora seja gravado um registro na tabela 
-- auditoria com a informao Registro excludo
drop trigger auditoria2;
CREATE TRIGGER auditoria2 AFTER DELETE ON editora
BEGIN
   INSERT INTO auditoria(editora_id,data, obs) 
   VALUES (old.id, datetime('now'), "Registro Excludo" );
END;

-- Excluindo para testar
delete from editora where id = 999;

-- Verificando
select * from auditoria;
select * from editora;




-- 4)Faa um join na tabela de livros com autores mostrando o 
-- ttulo do livro e do autor.
-- Tradicional INNER JOIN
select l.titulo, a.nome
from livro l, autor a
where a.id = l.autor_id;

-- Outra forma de INNER JOIN
select titulo, nome
from autor INNER JOIN livro
ON autor.id = livro.autor_id;

-- 5)Faa um join na tabela de livros com autores mostrando o ttulo 
-- do livro e do autor usando agora um LEFT OUTER JOIN para mostrar 
-- inclusive os livros que no tenham autor definido.
select  titulo, nome
from livro LEFT OUTER JOIN autor
ON autor.id = livro.estilo_id;

-- 6)Escreva um bloco de transao que exclua o livro de cdigo 1 e 
-- insira um novo livro de cdigo 50. Mas d um rollback na transao.
/*
begin transaction;
delete from livro where id = 1;
insert into livro(id,titulo) values (50,"Teste");
rollback;
*/

-- Verificando
select * from livro;

-- 7)Escreva um bloco de transao que exclua o livro de cdigo 3 e insira 
-- um novo livro de cdigo 3. Feche a transao com Commit.
/*
begin transaction;
delete from livro where id = 3;
insert into livro(id,titulo) values (3,"SQL de A a Z");
commit;
*/

-- Verificando
select * from livro;

/* 8)	Fazer um select no Banco e retornar os seguintes campos:
	Nome do autor em letras maisculas
	Nome da categoria em letras minsculas
	As 10 primeiras letras do nome do livro
	Valor do Livro
	Valor do Livro com desconto de 7%
Ateno: Trata-se de UM NICO COMANDO SELECT para retornar todos os campos acima. */
select 
upper(a.nome), 
lower(e.nome),
substr(l.titulo,1,10),
l.precovenda,
l.precovenda * 0.93

from livro l, autor a, estilo e
where a.id = l.autor_id
and e.id = l.estilo_id
;

-- 9)	Faa um select trazendo o nome do livro, cdigo do estilo, cdigo 
-- do autor e cdigo da editora. Caso algum campo desse esteja 
-- NULL mostre a mensagem SEM CADASTRO.
select titulo, 
       ifnull(estilo_id,"SEM CADASTRO"),
       ifnull(autor_id,"SEM CADASTRO"),
       ifnull(editora_id,"SEM CADASTRO")
       
from livro;

-- 10)Mostre o nome das 6 primeiras editoras em ordem 
-- alfabtica e em letras maisculas.
select upper(nome )
from editora
order by nome
limit 6;

-- 11)Faa um select mostrando o nome do livro e o 
-- tamanho em caracteres do nome
select titulo, length(titulo)
from livro;

-- 12)Exiba a data atual
select date('now');

-- 13)Exiba a verso do SQLite
select sqlite_version();

-- 14)Exiba o nome das editoras substituindo a letra a por X
select replace(nome,"a","X")
from editora;

-- 15)Grite bem alto TERMINEI TODOS OS EXERCCIOS!!!!!!! ;-)

